﻿CREATE PROCEDURE [dbo].[spCancelOrder]
	@OrderID int,
	@CancelReasonID int
AS
	declare @OrderStatus char(1)
	select @OrderStatus = OrderStatusCode from tblOrderHeader where OrderID = @OrderID

	-- If the order is already canceled then just return
	if @OrderStatus = 'C'
		return 0

	-- If the order status is not pending, paid or on backorder then throw an error
	if @OrderStatus not in ('P', 'E', 'B')
		raiserror( 'Order is not in an appropriate state to be canceled', 16, -1)

	begin transaction

	begin try
		-- If the order has been paid then generate a refund
		if @OrderStatus = 'E'
		begin
			exec spNewRefunds @OrderID, 0

			update tblRefund set Cost = MaxCost where OrderID = @OrderID
		end
	
		-- Cancel any existing inventory allocations
		update T1 set InventoryTransactionStatusCode = 'X'
		from tblInventoryTransaction T1
		where T1.OrderID = @OrderID
			and InventoryTransactionStatusCode not in ( 'X', 'L', 'C' )

		update tblOrderHeader set OrderStatusCode = 'C',
			CanceledDate = GETDATE(),
			CanceledReasonID = @CancelReasonID
		where OrderID = @OrderID

		commit
	end try
	begin catch
		declare @error_message varchar(max)
		if @@TRANCOUNT > 0
			rollback transaction

		SELECT @error_message = ERROR_MESSAGE()
		RAISERROR(@error_message, 16, 1)
		return 1
	end catch
RETURN 0